﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace BWServices
{
    public class DataManager
    {
        private SqlConnection myConnection = null;

        public DataManager()
        {
            myConnection = new SqlConnection(Properties.Settings.Default.myConnect);
            if (myConnection.State == ConnectionState.Closed) myConnection.Open();
        }

        public void WaitEnd()
        {
            while (myConnection.State == ConnectionState.Fetching) ;
        }

        string[] GetParametersName(string str)
        {
            int s = str.IndexOf(" ") + 1;
            
            string ps = str.Substring(s, (str.Length - s));
            if (ps == "") return null;
            string[] result = ps.Split(',');
            return result;
        }
        string GetCommander(string str)
        {
            int s = str.IndexOf("(");
            string cmd = str.Substring(0, s);
            return cmd;
        }
        private Type GetTypeByName(string name)
        {
            string type_name = name.ToLower();
            switch (type_name)
            {
                case "int":
                    return typeof(int);
                case "varchar":
                    return typeof(string);
                case "datetime":
                    return typeof(DateTime);
            }
            return typeof(string);
        }

        private DataTable DataReaderToTable(SqlDataReader reader, string table_name)
        {
            DataTable result = null;

            if (reader == null || !reader.HasRows || reader.IsClosed) return null;
            result = new DataTable(table_name);

            //create table struct
            for (int i = 0; i < reader.FieldCount; i++)
            {
                DataColumn col = new DataColumn();
                col.ColumnName = reader.GetName(i);

                string type_name = reader.GetDataTypeName(i);
                col.DataType = GetTypeByName(type_name);
                result.Columns.Add(col);
            }


            //fill data into the table
            while (reader.Read())
            {
                DataRow row = result.NewRow();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    //if (row[i].GetType() == typeof(string))
                    //{
                    //    row[i] = reader.GetString(i);// Encoding.Convert(Encoding.UTF8, Encoding.Default, reader.GetBytes());
                    //}
                    //else
                    //{
                    row[i] = reader.GetValue(i);
                    //}
                }
                result.Rows.Add(row);
            }
            return result;
        }
        public DataTable command_table(string cmd, object[] p)
        {
            SqlCommand command = new SqlCommand("exec " + cmd);
            string[] pname = GetParametersName(cmd);
            if (pname != null && p != null)
            {
                if (pname.Length == p.Length)
                {
                    for (int i = 0; i < pname.Length; i++)
                    {
                        command.Parameters.Add(new SqlParameter(pname[i], p[i]));
                    }
                }
            }

            DataTable result = null;// new DataTable("result");
            if (myConnection.State == ConnectionState.Closed) myConnection.Open();

            command.Connection = myConnection;
            SqlDataReader reader = command.ExecuteReader();

            result = DataReaderToTable(reader, "result");

            reader.Close();
            return result;
        }
        public int command_int(string cmd, object[] p)
        {
            int result = -1;
            
            SqlCommand command = new SqlCommand("exec "+cmd);
            string[] pname = GetParametersName(cmd);

            if (pname != null && p != null)
            {
                if (pname.Length == p.Length)
                {
                    for (int i = 0; i < pname.Length; i++)
                    {
                        command.Parameters.Add(new SqlParameter(pname[i], p[i]));
                    }
                }
            }

            if (myConnection.State == ConnectionState.Closed) myConnection.Open();

            command.Connection = myConnection;
            object r = command.ExecuteScalar();
            if (r == null) return -1;
            result = int.Parse(r.ToString());

            return result;
        }
        public decimal command_decimal(string cmd, object[] p)
        {
            decimal result = -1;

            SqlCommand command = new SqlCommand("exec " + cmd);
            string[] pname = GetParametersName(cmd);
            if (pname != null && p != null)
            {
                if (pname.Length == p.Length)
                {
                    for (int i = 0; i < pname.Length; i++)
                    {
                        command.Parameters.Add(new SqlParameter(pname[i], p[i]));
                    }
                }
            }

            if (myConnection.State == ConnectionState.Closed) myConnection.Open();

            command.Connection = myConnection;
            object r = command.ExecuteScalar();
            if (r == null) return -1;
            result = decimal.Parse(r.ToString());
            

            return result;
        }
    }
}